package com.sanbianyinwu.www.Public.Message;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.sanbianyinwu.www.Public.JDBConnection;

public class MsDBconnect extends JDBConnection {
	
	public static int CreateMs(Message_Info MI){	
		//创建一条新纪录
		//MI对象不需要messageID,用户名,管理员名称，已读标记和标识,加了也不会写进去
		//MI对象只需要写userID,adminID,message,subtime就可以了
		int row = 0;
		JDBConnection con=new JDBConnection();
		String sql = "insert into yx_message(userID,adminID,message,subtime)" +
					" values(?,?,?,?)";
		PreparedStatement ps = con.CreatePreparedStatement(sql);
		try{	//写入信息
			ps.setInt(1, MI.getUserID());
			ps.setInt(2,MI.getAdminID());
			ps.setString(3,MI.getMessage());
			ps.setLong(4,MI.getSubtime());
			row = ps.executeUpdate();
			if(row > 0)
				System.out.println("成功添加了" + row + "条数据");
		}catch(SQLException e){
			e.printStackTrace();
		}finally{
			try {
				ps.close();
			} catch (SQLException e) {
			e.printStackTrace();
			}
		}
		con.closeConnection();
		return row;
	}
	
	public static int DeleteMs(int id){
		//按id号删除记录
		JDBConnection con=new JDBConnection();
		String sql = "delete from yx_message where messageID = " + 
					((Integer)id).toString();
		int iCount = con.executeUpdate(sql);
		return iCount;
	}
	
	public static Message_Info SelectMs(int Messageid){
		//按id号删除记录
		JDBConnection con=new JDBConnection();
		String sql = "select * from yx_ms where messageID = " + 
					Messageid;
		ResultSet rs = con.executeQuery(sql);
		Message_Info MI = new Message_Info();
		try{
			while(rs.next()){
				
				MI.setMessageID(rs.getInt("messageID"));
				MI.setUserID(rs.getInt("userID"));
				MI.setUserName(rs.getString("userName"));
				MI.setAdminID(rs.getInt("adminID"));
				MI.setAdminName(rs.getString("adminName"));
				MI.setMessage(rs.getString("message"));
				MI.setSubtime(rs.getLong("subtime"));
				MI.setHave_read(rs.getInt("have_read"));
				MI.setMark(rs.getInt("mark"));
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
		
		return MI;
	}
	
	public static List<Message_Info> RetrieveMS(MS_attribute MS_a){
		//查找操作
		String sql = "select * from yx_ms" + Type_Sql(MS_a.user_type , MS_a.id) 
				+ printType_Sql(MS_a.print_type);
		sql+=" order by subtime desc";
		if(MS_a.limit)
			sql += " limit " + Page_sql(MS_a.page);
		System.out.println(sql);
		
		JDBConnection con = new JDBConnection();
		ResultSet rs = con.executeQuery(sql);
		List <Message_Info> L = new ArrayList<Message_Info>();
		try{
			while(rs.next()){
				Message_Info MI = new Message_Info();
				MI.setMessageID(rs.getInt("messageID"));
				MI.setUserID(rs.getInt("userID"));
				MI.setUserName(rs.getString("userName"));
				MI.setAdminID(rs.getInt("adminID"));
				MI.setAdminName(rs.getString("adminName"));
				MI.setMessage(rs.getString("message"));
				MI.setSubtime(rs.getLong("subtime"));
				MI.setHave_read(rs.getInt("have_read"));
				MI.setMark(rs.getInt("mark"));
				L.add(MI);
			}
			rs.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		con.closeConnection();
		return L;
	}
	
	private static String Type_Sql(int type,Integer id){
		//类型在此！！
		//用户(1)
		//管理员(2)
		//全部都看(3)
		//用户个人(4)
		//管理员个人（5）
		String temp = null;
		if(type == 1) 
			//user
			temp = " where adminID = 0 ";
		else if(type == 2)
			//admin
			temp = " where userID = 0 ";
		else if(type == 3)
			temp = " where userID >= 0 ";
		else if(type == 4)
			temp = " where userID = " + id.toString() + " ";
		else if(type == 5)
			temp = " where adminID = " + id.toString() + " ";
		return temp;
	}
	
	private static String printType_Sql(int type){
		//输出类型在此！
		String temp = "";
		if(type == 1) 
			temp = "";
		else if(type == 2)
			temp = " and have_read = 0 ";
		else if(type == 3)
			temp = " and have_read = 1 ";
		else if(type == 4)
			temp = " and mark = 1 ";
		return temp;
	}
	
	private static String Page_sql(int page){
		//页数在此！！
		Integer each_page_count = 10;
		Integer pagestart = (page - 1) * each_page_count;
		return pagestart.toString() + ", " + each_page_count.toString();
	}
	
	public static int UpdateMS_Have_Read(int MS_id,boolean read){
		//将信息设为已读或未读,read = false为未读，=true为已读
		JDBConnection con=new JDBConnection();
		String temp = read ? "1" : "0";
		String sqlupdate = "update yx_ms set have_read = "+ temp +" where messageID = "
				+ MS_id;
		int iCount = con.executeUpdate(sqlupdate);
		return iCount;
	}
	
	public static int UpdateMS_Mark(int id , int mark){		
		//标记+取消标记,mark为1(被标记)或0（未被标记）
		//按id标记留言信息
		JDBConnection con=new JDBConnection();
		String sql = "update yx_ms set mark = "+ ((Integer)mark).toString() +
					" where messageID = " + ((Integer)id).toString();
		int iCount = con.executeUpdate(sql);
		return iCount;
	}
	
	public static int RetrieveMS_Count(MS_attribute MS_a,int Message_Num_a_page){
		//查找页数操作
				String sql = "select count(*) from yx_ms" + Type_Sql(MS_a.user_type , MS_a.id) 
						+ printType_Sql(MS_a.print_type);
				JDBConnection con = new JDBConnection();
				ResultSet rs = con.executeQuery(sql);
				int count = 0;
				try{
					if(rs.next()){
						count = rs.getInt(1);
					}
					rs.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
				con.closeConnection(); 
				return count % Message_Num_a_page == 0 ? count / Message_Num_a_page : (count / Message_Num_a_page + 1);
	}
}
